package com.dao.impl;

import com.dao.Employee_infoDao;
import com.db.ConnectionFactory;
import com.entry.Employee_info;
import com.util.PageModel;
import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;

public class Employee_infoDaoImpl implements Employee_infoDao {
    /*添加员工信息*/
    @Override
    public int add(Employee_info employee_info) {
        Connection conn = null;
        String sql = "insert into employee_info values(?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement prst = null;
        int i = 0;
        try {
            conn = ConnectionFactory.getConnection();
            //向员工信息表中插入信息
            prst = conn.prepareStatement(sql);
            prst.setString(1, employee_info.getUsername());
            prst.setString(2, employee_info.getEname());
            prst.setString(3, employee_info.getIdcode());
            prst.setString(4, employee_info.getSsex());
            prst.setString(5, employee_info.getNation());
            prst.setString(6, employee_info.getPolitical());
            prst.setString(7, employee_info.getBirthday());
            prst.setString(8, employee_info.getHomeaddr());
            prst.setString(9, employee_info.getMobile());
            prst.setString(10, employee_info.getZipcode());
            prst.setString(11, employee_info.getReceiver());
            prst.setString(12, employee_info.getReceiver());
            i = prst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    /*根据username查询员工信息*/
    @Override
    public PageModel<Employee_info> findByOne(int pageSize, int pageNo, String username) {
        ArrayList<Employee_info> arrys = new ArrayList<Employee_info>();
        Connection conn = null;
        PreparedStatement prst = null;
        ResultSet rs = null;
        String sql = "select * from employee_info where username like'%"+username+"%'";
        PageModel<Employee_info> pm = new PageModel<Employee_info>(pageSize, pageNo, total(sql));
        try {
            conn = ConnectionFactory.getConnection();
            prst = conn.prepareStatement(sql + " limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = prst.executeQuery();
            while(rs.next()) {
                Employee_info e = new Employee_info();
                e.setUsername(rs.getString(1));
                e.setEname(rs.getString(2));
                e.setIdcode(rs.getString(3));
                e.setSsex(rs.getString(4));
                e.setNation(rs.getString(5));
                e.setPolitical(rs.getString(6));
                e.setBirthday(rs.getString(7));
                e.setHomeaddr(rs.getString(8));
                e.setMobile(rs.getString(9));
                e.setZipcode(rs.getString(10));
                e.setConaddr(rs.getString(11));
                e.setReceiver(rs.getString(12));
                arrys.add(e);
            }
            pm.setData(arrys);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pm;
    }

    /*查询全部员工信息*/
    @Override
    public PageModel<Employee_info> findAll(int pageSize, int pageNo) {
        ArrayList<Employee_info> arrys = new ArrayList<Employee_info>();
        Connection conn = null;
        PreparedStatement prst = null;
        ResultSet rs = null;
        String sql = "select * from employee_info where 1=1 ";
        PageModel<Employee_info> pm = new PageModel<Employee_info>(pageSize, pageNo, total(sql));
        try {
            conn = ConnectionFactory.getConnection();
            prst = conn.prepareStatement(sql + " limit " + (pm.getPageNo() - 1) * pm.getPageSize() + "," + pm.getPageSize());
            rs = prst.executeQuery();
            while (rs.next()) {
                Employee_info e = new Employee_info();
                e.setUsername(rs.getString(1));
                e.setEname(rs.getString(2));
                e.setIdcode(rs.getString(3));
                e.setSsex(rs.getString(4));
                e.setNation(rs.getString(5));
                e.setPolitical(rs.getString(6));
                e.setBirthday(rs.getString(7));
                e.setHomeaddr(rs.getString(8));
                e.setMobile(rs.getString(9));
                e.setZipcode(rs.getString(10));
                e.setConaddr(rs.getString(11));
                e.setReceiver(rs.getString(12));
                arrys.add(e);
            }
            pm.setData(arrys);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (rs!=null)
                    rs.close();
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }

    @Override
    public int delete(String username) {
        Connection conn = null;
        PreparedStatement prst = null;
        int i = 0;
        String sql = "delete from employee_info where username=?";
        try {
            conn = ConnectionFactory.getConnection();
            prst = conn.prepareStatement(sql);
            prst.setString(1, username);
            i = prst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    @Override
    public int update(Employee_info e) {
        Connection conn = null;
        PreparedStatement prst = null;
        int i = 0;
        String sql = "update employee_info set idcode=?,nation=?,political=?,homeaddr=? where username=?";
        try {
            conn = ConnectionFactory.getConnection();
            prst = conn.prepareStatement(sql);
            prst.setString(1, e.getIdcode());
            prst.setString(2, e.getNation());
            prst.setString(3, e.getPolitical());
            prst.setString(4, e.getHomeaddr());
            prst.setString(5, e.getUsername());
            i = prst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return i;
    }

    /*查询一条记录*/
    @Override
    public Employee_info findUser(String username) {
        Employee_info e = new Employee_info();;
        Connection conn = null;
        PreparedStatement prst = null;
        ResultSet rs = null;
        String sql = "select * from employee_info where username=?";
        try {
            conn = ConnectionFactory.getConnection();
            prst = conn.prepareStatement(sql);
            prst.setString(1, username);
            rs = prst.executeQuery();
            if (rs.next()) {
                e.setUsername(rs.getString(1));
                e.setEname(rs.getString(2));
                e.setIdcode(rs.getString(3));
                e.setSsex(rs.getString(4));
                e.setNation(rs.getString(5));
                e.setPolitical(rs.getString(6));
                e.setBirthday(rs.getString(7));
                e.setHomeaddr(rs.getString(8));
                e.setMobile(rs.getString(9));
                e.setZipcode(rs.getString(10));
                e.setConaddr(rs.getString(11));
                e.setReceiver(rs.getString(12));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NamingException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (rs!=null)
                    rs.close();
                if (prst != null)
                    prst.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return e;
    }

    public int total(String sql) {
        int recordCount = 0;
        String countsql = "select count(*) "
                + sql.substring(sql.indexOf("from"), sql.length());

        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(countsql);
            if (rs.next()) {
                recordCount = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recordCount;
    }
}
